USE [bmig_Mensual_Post_cadena]
GO

IF EXISTS (SELECT name FROM   sysobjects WHERE  name = 'WASP_M0011_CARGATBL_Seg_Salud' AND type = 'P')
    DROP PROCEDURE [dbo].[WASP_M0011_CARGATBL_Seg_Salud]
GO

CREATE PROCEDURE [dbo].[WASP_M0011_CARGATBL_Seg_Salud]

AS


BEGIN 
		INSERT INTO  seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg1 
		FROM codvalSalud 
		WHERE cod_seg1 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg2 
		FROM codvalSalud 
		WHERE cod_seg2 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg3 
		FROM codvalSalud 
		WHERE cod_seg3 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg4 
		FROM codvalSalud 
		WHERE cod_seg4 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg5
		FROM codvalSalud 
		WHERE cod_seg5 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg6 
		FROM codvalSalud 
		WHERE cod_seg6 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg7
		FROM codvalSalud 
		WHERE cod_seg7 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg8
		FROM codvalSalud 
		WHERE cod_seg8 = 106
END


BEGIN 
		INSERT INTO seg_saludM 
		SELECT  'D', '', '', '', '', '',  '', codvalSalud.ppsuc , codvalSalud.ppoper, codvalSalud.ppcta, '', '',
			 '', '', '', '', '', '',  '', '', '', '', '', '', '', val_seg9
		FROM codvalSalud 
		WHERE cod_seg9 = 106
END


BEGIN 
		INSERT INTO seg_saludFo
		SELECT max(dotelf) fono, ctnro
		FROM seg_saludM (nolock),
			 fsr006  (nolock)
		WHERE RutAsegurado = ctnro and dotelf <> '' and dotelf <> '0'
		GROUP BY ctnro

END


BEGIN 
		UPDATE seg_saludM SET TelefonoAsegurado = convert(varchar(9), fono )
		FROM seg_saludFo (nolock)
		WHERE ctnro = RutAsegurado
END


BEGIN 
		--UPDATE  seg_saludM  SET periodopro = 200710 , fechaenvio = 20071131

		UPDATE  seg_saludM  SET NroCorrelativoArchivo  = '000001', TipoMovimiento = '01'

		UPDATE  seg_saludM  SET NroProducto  = '0106'
END


BEGIN 
		INSERT INTO seg_saludCu
		SELECT	fsd601.ppoper operacion, 
				fsd601.ppcta cuenta, 
				fsd601.ppsuc sucursal, 
				count(*) cuotas 
		FROM	seg_saludM (nolock),
				fsd601 (nolock)
		WHERE	fsd601.ppoper = seg_saludM.NroCredito and
				fsd601.ppsuc  = seg_saludM.CodAgen and
				fsd601.ppcta  = seg_saludM.RutAsegurado and
				fsd601.ppsbop = 0
		GROUP BY fsd601.ppoper, fsd601.ppcta, fsd601.ppsuc
END


BEGIN 
		UPDATE seg_saludM SET cuotaspactadas = cuotas
		FROM   seg_saludCu (nolock) 
		WHERE seg_saludCu.operacion = seg_saludM.NroCredito and
			  seg_saludCu.sucursal  = seg_saludM.CodAgen and
			  seg_saludCu.cuenta    = seg_saludM.RutAsegurado 
END


BEGIN 
		UPDATE seg_saludM  SET PrimaBrutaCaja = 1.90
		--			case
		--				      when (cuotaspactadas between 3 and 24)then  (0.6700)
		--				      when (cuotaspactadas between 25 and 36)then (1.2100)
		--				      when (cuotaspactadas between 37 and 60)then (1.7100)
		 --                                     else 0 end

		UPDATE seg_saludM SET NroPoliza= case
							  when (CuotasPactadas between 3 and 24) then '10329484'
 									  when (CuotasPactadas between 25 and 36)then '10329483'
							  when (CuotasPactadas between 37 and 60)then '10329482'
									 end
END


BEGIN 
		UPDATE  seg_saludM SET DVAsegurado  = substring(fsd002.pfndoc,len(fsd002.pfndoc),1),
				nombreasegurado             = cast(rtrim(ltrim(fsd002.pfape1))+' '+rtrim(ltrim(fsd002.pfape2))+' ' +rtrim(ltrim(fsd002.pfnom1))+' '+rtrim(ltrim(fsd002.pfnom2))as varchar(50)) 
		FROM    fsr008 (nolock),
				fsd002 (nolock)
		WHERE seg_saludM.rutasegurado = fsr008.ctnro and
			  fsr008.Pendoc           = fsd002.Pfndoc
END


BEGIN 

		UPDATE seg_saludM SET DireccionAsegurado = docall+cast(donro as varchar)
		FROM   fsd006 (nolock)
		WHERE ctnro = rutasegurado
END


BEGIN 
		UPDATE seg_saludM SET CodigoComuna = z07310COM, CodigoRegion = z07339REG
		FROM   z07331 (nolock)
		WHERE  z07331cta = rutasegurado
END


BEGIN 
		UPDATE seg_saludM SET VigenciaDesde = convert(varchar,fsd010.aofval,112), VigenciaHasta = convert(varchar,dateadd(day, -1, dateadd(year,1, fsd010.aofval)),112)
		FROM   fsd010 (nolock)
		WHERE  fsd010.aooper  = seg_saludM.NroCredito and
			   fsd010.aosuc   = seg_saludM.CodAgen and
			   fsd010.aocta   = seg_saludM.RutAsegurado and
			   fsd010.aosbop  = 0
END


BEGIN 
		UPDATE seg_saludM SET DescripcionComuna = z07310dsc
		FROM  z07310 (nolock)
		WHERE seg_saludM.CodigoComuna = z07310.z07310com
END


BEGIN 
		UPDATE seg_saludM SET DescripcionRegion = z07339dsc
		FROM  z07339 (nolock)
		WHERE seg_saludM.CodigoRegion = z07339.z07339reg
END


BEGIN 			
		UPDATE seg_saludM SET PrimaNetaCaja = cast(cast((PrimaBrutaCaja/1.19) as decimal(10,4))as varchar)
		
END